package cn.ffcs.contract.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.echoice.hibernate.HibernateEntityDao;
import org.echoice.paper.PageBean;
import org.springframework.jdbc.core.RowMapper;

import cn.ffcs.bpmpvc.model.BpmAppInfo;
import cn.ffcs.contract.dao.ContractRemindDao;
import cn.ffcs.contract.model.ContractBussout;
import cn.ffcs.contract.model.ContractInfo;
import cn.ffcs.contract.model.ContractRemind;
import cn.ffcs.contract.web.view.ContractBussoutView;

public class ContractRemindDaoImpl extends HibernateEntityDao<ContractRemind> implements
		ContractRemindDao {

	public List<ContractRemind> findCurrentList() {
		// TODO Auto-generated method stub
		String sql="select t.id,t.cond_tag,t.cond_time,t.cond_note,t.cond_do,t.contract_id,t.procinst_id from T_CONTRACT_REMIND t" +
				" where t.cond_time is not null and to_char(t.cond_time,'yyyy-MM-DD')<=to_char(sysdate,'yyyy-MM-DD') and (t.cond_do='1' or t.cond_do is null)";
		System.out.println("sql:"+sql);
		List list=getJdbcTemplate().query(sql, new RowMapper(){

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ContractRemind contractRemind=new ContractRemind(); 
				contractRemind.setId(Integer.parseInt(rs.getString(1)));
				contractRemind.setCondTag(rs.getString(2));
				contractRemind.setCondTime(rs.getDate(3));
				contractRemind.setCondNote(rs.getString(4));
				contractRemind.setContractId(rs.getString(6));
				return contractRemind;
			}});
		return list;
	}

	
	public List<ContractRemind> findCurrentListNew() {
		// TODO Auto-generated method stub
		String sql="select t.id,t.cond_tag,t.cond_time,t.cond_note,t.cond_do,t.contract_id,t.procinst_id," +
				"t.starat_time,t.end_time,t.next_time,t.mon_day,t.time_type from T_CONTRACT_REMIND t" +
				" where t.next_time is not null and to_char(t.next_time,'yyyy-MM-DD')=to_char(sysdate,'yyyy-MM-DD')";
		System.out.println("sql:"+sql);
		List list=getJdbcTemplate().query(sql, new RowMapper(){

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ContractRemind contractRemind=new ContractRemind(); 
				contractRemind.setId(Integer.parseInt(rs.getString(1)));
				contractRemind.setCondTag(rs.getString(2));
				contractRemind.setCondTime(rs.getDate(3));
				contractRemind.setCondNote(rs.getString(4));
				contractRemind.setContractId(rs.getString(6));
				contractRemind.setProcinstId(rs.getString(7));
				contractRemind.setStartTime(rs.getDate(8));
				contractRemind.setEndTime(rs.getDate(9));
				contractRemind.setNextTime(rs.getDate(10));
				String monthDay=rs.getString(11);
				if(StringUtils.isNotBlank(monthDay)){
					contractRemind.setMonDay(Integer.valueOf(monthDay));
				}
				contractRemind.setTimeType(rs.getString(12));
				
				return contractRemind;
			}});
		return list;
	}
	
	public List<BpmAppInfo> findDue3MonthContractList(){
		String sql="select id, contract_name, is_remind, duetime from v_contract_info_dueremind";
		List list=getJdbcTemplate().query(sql, new RowMapper(){

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				BpmAppInfo appInfo=new BpmAppInfo();
				appInfo.setAppId(rs.getString(1));
				appInfo.setAppTitle(rs.getString(2));
				
				return appInfo;
			}});
		return list;
	}
	
	public PageBean findValidContractPage(ContractInfo searchForm,int pageNo,int pageSize){
		String sql="select t.id,t.SIGN_CODE,t.CONTRACT_NAME,t.SERIAL,t.SIGNATORIES_NAME,t.CREATOR_NAME,t.EFFECT_TIME,";
		sql+="t.MATURITY_TIME,t.POSTPONE_TIME,t.Potency";
		sql+=" from v_contract_info t where t.Potency!='失效'";
		//
		List paramList=new ArrayList();
		
		if(searchForm!=null){
			if(StringUtils.isNotBlank(searchForm.getContractName())){
				sql+=" and t.CONTRACT_NAME like ?";
				paramList.add("%"+searchForm.getContractName()+"%");
			}
			
			if(StringUtils.isNotBlank(searchForm.getSerial())){
				sql+=" and t.SERIAL like ?";
				paramList.add("%"+searchForm.getSerial()+"%");
			}
			
			if(StringUtils.isNotBlank(searchForm.getSignatoriesName())){
				sql+=" and t.SIGNATORIES_NAME like ?";
				paramList.add("%"+searchForm.getSignatoriesName()+"%");
			}
			
			
			if(StringUtils.isNotBlank(searchForm.getCreatorName())){
				sql+=" and t.CREATOR_NAME like ?";
				paramList.add("%"+searchForm.getCreatorName()+"%");
			}
		}
		sql+=" order by to_number(t.id) desc";
		PageBean pageBean=super.pagedQuerySQLJdbcTemplate(sql, new RowMapper(){

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ContractInfo obj=new ContractInfo();
				obj.setSignCode(rs.getString("SIGN_CODE"));
				obj.setContractName(rs.getString("CONTRACT_NAME"));
				obj.setSerial(rs.getString("SERIAL"));
				obj.setSignatoriesName(rs.getString("SIGNATORIES_NAME"));
				obj.setCreatorName(rs.getString("CREATOR_NAME"));
				obj.setEffectTime(rs.getTimestamp("EFFECT_TIME"));
				obj.setMaturityTime(rs.getTimestamp("MATURITY_TIME"));
				obj.setPostponeTime(rs.getTimestamp("POSTPONE_TIME"));
				obj.setPotency(rs.getString("Potency"));
				obj.setId(rs.getInt("id"));
				return obj;
			}}, pageNo, pageSize, paramList.toArray());
		
		return pageBean;
	}
	
	
	public PageBean findContractBussOutPage(ContractBussoutView searchForm,int pageNo,int pageSize){
		String sql="select t1.req_code,t2.contract_name,t2.signatories_name,t3.name contractTypeName,";
		sql+="t1.out_type,t1.user_id,t4.name cn_name,t1.apply_time,t1.procinst_id";
		sql+=" from T_CONTRACT_BUSSOUT t1,t_contract_info t2,t_sys_dict t3, t_organ_person t4";
		sql+=" where t1.contract_id=t2.id and t2.contract_type=t3.id(+) and t1.user_id=t4.id";
		//
		List paramList=new ArrayList();
		
		if(searchForm!=null){
			if(StringUtils.isNotBlank(searchForm.getContractName())){
				sql+=" and t.CONTRACT_NAME like ?";
				paramList.add("%"+searchForm.getContractName()+"%");
			}
			
			
			if(StringUtils.isNotBlank(searchForm.getSignatoriesName())){
				sql+=" and t.SIGNATORIES_NAME like ?";
				paramList.add("%"+searchForm.getSignatoriesName()+"%");
			}
			
			if(StringUtils.isNotBlank(searchForm.getStartTime())){
				sql+=" and to_char(t1.apply_time,'yyyy-MM-dd')>=?";
				paramList.add(searchForm.getStartTime());
			}
			
			if(StringUtils.isNotBlank(searchForm.getEndTime())){
				sql+=" and to_char(t1.apply_time,'yyyy-MM-dd')<=?";
				paramList.add(searchForm.getEndTime());
			}
		}
		sql+=" order by t1.req_code desc";
		logger.info(sql);
		PageBean pageBean=super.pagedQuerySQLJdbcTemplate(sql, new RowMapper(){

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ContractBussoutView obj=new ContractBussoutView();
				obj.setReqCode(rs.getString("req_code"));
				obj.setContractName(rs.getString("contract_name"));
				obj.setSignatoriesName(rs.getString("signatories_name"));
				obj.setContractTypeName(rs.getString("contractTypeName"));
				obj.setOutType(rs.getLong("out_type"));
				obj.setUserCnName(rs.getString("cn_name"));
				obj.setApplyTime(rs.getTimestamp("apply_time"));
				obj.setProcinstId(rs.getString("procinst_id"));
				return obj;
			}}, pageNo, pageSize, paramList.toArray());
		
		return pageBean;
	}
	
	public ContractBussout getContractBussout(String proInstId){
		String sql="select t from ContractBussout t where t.procinstId=?";
		List list=getHibernateTemplate().find(sql, proInstId);
		if(list!=null&&list.size()>0){
			return (ContractBussout)list.get(0);
		}
		return null;
	}
	
	public int updateDue3MonthRemind(String id,String isRemind){
		String sql="update t_contract_info t set t.is_remind=? where t.id=?";
		return getJdbcTemplate().update(sql, new Object[]{isRemind,id});
	}
	
	public int updateRemind(String id,String isDo,String procinstId){
		String sql="update T_CONTRACT_REMIND t set t.cond_do=?,t.procinst_id=? where t.id=?";
		return getJdbcTemplate().update(sql, new Object[]{isDo,procinstId,id});
	}
	
	public List<ContractRemind> findListByContractId(String contractId) {
		// TODO Auto-generated method stub
		String hql="select t from ContractRemind t where t.contractId=? order by t.condTag asc";
		List<ContractRemind> list=getHibernateTemplate().find(hql, contractId);
		return list;
	}
	
	public ContractRemind getByProcinstId(String procinstId){
		String hql="select t from ContractRemind t where t.procinstId=?";
		List list=getHibernateTemplate().find(hql, procinstId);
		if(list!=null&&list.size()>0){
			return (ContractRemind)list.get(0);
		}
		return null;
	}
	
	public ContractRemind getByTag(String condTag,String contractId){
		String hql="select t from ContractRemind t where t.condTag=? and t.contractId=?";
		List list=getHibernateTemplate().find(hql, new Object[]{condTag,contractId});
		if(list!=null&&list.size()>0){
			return (ContractRemind)list.get(0);
		}
		return null;
	}
	
	public void saveRemind(List<ContractRemind> contractRemindlist,String contractId){
		List<ContractRemind> dbList=findListByContractId(contractId);
		for (ContractRemind contractRemind : contractRemindlist) {
			if(dbList!=null&&dbList.size()>0){
				for (ContractRemind contractRemindDB : dbList) {
					if(contractRemind.getCondTag().equalsIgnoreCase(contractRemindDB.getCondTag())){
						//contractRemindDB.setCondNote(contractRemind.getCondNote());
						//contractRemindDB.setCondTime(condTime)
						
						contractRemind.setId(contractRemindDB.getId());
						break;
					}
				}
			}
			//save(contractRemind);
			merge(contractRemind);
		}
	}
	
	public ContractRemind getNextRemind(ContractRemind curContractRemind){
		String sql="select t.id,t.cond_tag,t.cond_time,t.cond_note,t.cond_do,t.contract_id,t.procinst_id from t_contract_remind t where t.contract_id=? and t.id>? and (t.cond_do is null or t.cond_do='1') and rownum=1 order by t.id asc";
		List<ContractRemind> list=getJdbcTemplate().query(sql, new Object[]{curContractRemind.getContractId(),curContractRemind.getId()}, new RowMapper(){

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ContractRemind contractRemind=new ContractRemind();
				contractRemind.setId(rs.getInt(1));
				contractRemind.setCondTag(rs.getString(2));
				contractRemind.setCondTime(rs.getDate(3));
				contractRemind.setCondNote(rs.getString(4));
				contractRemind.setCondDo(rs.getString(5));
				contractRemind.setProcinstId(rs.getString(6));
				return contractRemind;
			}
			
		});
		if(list!=null&&list.size()>0){
			return list.get(0);
		}
		return null;
	}
	
	public void saveOrUpdateRemind(List<ContractRemind> list){
		if(list.size()==0)return;
		for (int i = 0; i < list.size(); i++) {
			ContractRemind remind=list.get(i);
			getHibernateTemplate().saveOrUpdate(remind);
		}
	}
	public ContractInfo getById(Integer id){
		String hql="select t from ContractInfo t where t.id=?";
		List list=getHibernateTemplate().find(hql, id);
		if(list!=null&&list.size()>0){
			return (ContractInfo)list.get(0);
		}
		return null;
	}
	public static void main(String[] args) {
		String sql="select t.id,t.cond_tag,t.cond_time,t.cond_note,t.cond_do,t.contract_id,t.procinst_id," +
		"t.starat_time,t.end_time,t.next_time,t.mon_day,t.time_type from T_CONTRACT_REMIND t" +
		" where t.next_time is not null and to_char(t.next_time,'yyyy-MM-DD')=to_char(sysdate,'yyyy-MM-DD')";
		System.out.println("sql:"+sql);
	}
}
